import json
import pymysql

global host
global user
global password
global port
global database
global table_person
global table_family
global table_card

def init_database():
    # 假设 JSON 数据存储在一个文件中，文件名为 example.json
    with open('./conf/config.json', 'r', encoding='utf-8') as f:
        data = json.load(f)

    global host
    global user
    global password
    global port
    global database
    global table_person
    global table_family
    global table_card

    # 解析 database_config
    database_config = data['database_config']
    tables = database_config['tables']
    connection = database_config['connection']

    # 解析表名
    table_person = tables['table_person']
    table_family = tables['table_family']
    table_card = tables['table_card']

    # 解析数据库连接信息
    host = connection['host']
    user = connection['user']
    password = connection['password']
    port = connection['port']
    database = connection['database']
    # charset = connection['charset']
    # cursorclass = connection['cursorclass']




class Person:
    def __init__(self, name=None,old_name=None, birthday=None, householder_name=None, relation=None, gender=None, nationality=None, address=None,
                 domicile_registration=None, education_level=None, marriage_status=None, image_path=None,information_text = None):
        # 如果传入了相应参数，则使用传入的值，否则将其设为 None
        self.name = name
        self.old_name = old_name
        self.birthday = birthday
        self.householder_name = householder_name
        self.relation = relation
        self.gender = gender
        self.nationality = nationality
        self.address = address
        self.domicile_registration = domicile_registration
        self.education_level = education_level
        self.marriage_status = marriage_status
        self.image_path = image_path
        self.information_text = information_text

    def print_all_parameters(self):
        print(f"Name: {self.name}")
        print(f"Old_name: {self.old_name}")
        print(f"Birthday: {self.birthday}")
        print(f"Householder Name: {self.householder_name}")
        print(f"Relation: {self.relation}")
        print(f"Gender: {self.gender}")
        print(f"Nationality: {self.nationality}")
        print(f"Address: {self.address}")
        print(f"Domicile Registration: {self.domicile_registration}")
        print(f"Education Level: {self.education_level}")
        print(f"Marriage Status: {self.marriage_status}")
        print(f"Image Path: {self.image_path}")
        print(f"Information Text: {self.information_text}")

    def insert_person_information(self,table_user):
        # print(table_user)
        # 连接数据库
        db = pymysql.connect(host=host, user=user, password=password, port=port, db=database)
        cursor = db.cursor()


        # check_sql = "SELECT * FROM {} WHERE name = %s AND birthday = %s AND gender = %s".format(table_user)
        # print(check_sql)
        # cursor.execute(check_sql, (self.name, self.birthday, self.gender))
        # result = cursor.fetchone()
        result = 0
        #插入前检测姓名、出生日期、性别，若已存在则跳过插入
        if result:
            print("Data already exists, skipping insert.")
        else:
            # 检查数据是否已存在
            check_sql = "SELECT * FROM {} WHERE name = %s AND birthday = %s AND gender = %s AND nationality=%s".format(table_user)
            cursor.execute(check_sql, (self.name, self.birthday, self.gender,self.nationality))
            result = cursor.fetchone()

            if result:
                # 数据已存在，执行更新操作
                print("数据已存在，执行更新操作")
                update_sql = """
                UPDATE {}
                SET old_name = %s,
                    householder_name = %s,
                    relation = %s,
                    nationality = %s,
                    address = %s,
                    domicile_registration = %s,
                    education_level = %s,
                    marriage_status = %s,
                    image_path = %s,
                    information_text = %s
                WHERE name = %s AND birthday = %s AND gender = %s
                """.format(table_user)
                try:
                    cursor.execute(update_sql, (
                    self.old_name, self.householder_name, self.relation, self.nationality, self.address,
                    self.domicile_registration,
                    self.education_level, self.marriage_status, self.image_path, self.information_text,
                    self.name, self.birthday, self.gender))
                    db.commit()
                    print("Data updated successfully.")
                except pymysql.MySQLError as e:
                    print(f"Error occurred during update: {e}")
                    db.rollback()
                finally:
                    # 关闭数据库连接
                    db.close()
            else:
                try:
                    sql = "INSERT INTO {} (name,old_name, birthday, householder_name, relation, gender, nationality, address, domicile_registration, " \
                          "education_level, marriage_status,image_path,information_text) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)".format(
                        table_user)

                    # 执行 SQL 插入操作
                    cursor.execute(sql, (self.name,self.old_name, self.birthday, self.householder_name, self.relation, self.gender, self.nationality, self.address, self.domicile_registration,
                                      self.education_level, self.marriage_status,self.image_path,self.information_text))
                    # 提交事务
                    db.commit()
                except pymysql.MySQLError as e:
                    # 若出现异常，打印错误信息并回滚事务
                    print(f"Error occurred: {e}")
                    db.rollback()
                finally:
                    # 关闭数据库连接
                    db.close()

def build_person_information_demo(table) -> None:
    db = pymysql.connect(host='localhost', user='root', password='chkx123', port=3306, db="archive_dome")
    cursor = db.cursor()
    sql = "show tables"
    cursor.execute(sql)

    # sql = "CREATE TABLE {}(name VARCHAR(10)," \
    #       "birthday VARCHAR(20)," \
    #       "householder_name VARCHAR(10)," \
    #       "relation VARCHAR(10)," \
    #       "gender VARCHAR(5)," \
    #       "nationality VARCHAR(20)," \
    #       "address VARCHAR(100)," \
    #       "domicile_registration VARCHAR(100)," \
    #       "education_level VARCHAR(20)," \
    #       "marriage_status VARCHAR(10));".format(table)

    # sql2 = "CREATE TABLE {}(name VARCHAR(10)," \
    #       "birthday VARCHAR(20)," \
    #       "householder_name VARCHAR(10)," \
    #       "relation VARCHAR(10)," \
    #       "gender VARCHAR(5)," \
    #       "nationality VARCHAR(20)," \
    #       "address VARCHAR(100)," \
    #       "domicile_registration VARCHAR(100)," \
    #       "education_level VARCHAR(20)," \
    #       "education_level VARCHAR(20)," \
    #       "education_level VARCHAR(20)," \
    #       "marriage_status VARCHAR(10));".format(table)

    sql = "CREATE TABLE {}(name VARCHAR(10)," \
           "old_name VARCHAR(10)," \
           "birthday VARCHAR(20)," \
           "householder_name VARCHAR(10)," \
           "relation VARCHAR(10)," \
           "gender VARCHAR(5)," \
           "nationality VARCHAR(20)," \
           "address VARCHAR(100)," \
           "domicile_registration VARCHAR(100)," \
           "education_level VARCHAR(20)," \
           "marriage_status VARCHAR(10)," \
           "image_path VARCHAR(200)," \
           "information_text text);".format(table)


    cursor.execute(sql)

def build_database() -> None:
    connection = pymysql.connect(host='localhost', user='root', password='chkx123')
    cursor = connection.cursor()
    database_name = database

    try:
        create_database_query = f"CREATE DATABASE IF NOT EXISTS {database_name}"
        cursor.execute(create_database_query)
        print(f"数据库 {database_name} 创建成功！")
    except pymysql.err as e:
        print(f"创建数据库时出现错误: {e}")

    finally:
        # 无论是否发生异常，都关闭游标和数据库连接
        if 'cursor' in locals():
            cursor.close()
        if 'connection' in locals():
            connection.close()

def drop_table(table_name):
    db = pymysql.connect(host='localhost', user='root', password='chkx123', port=3306, db="archive_dome")
    cursor = db.cursor()
    sql = "show tables"
    cursor.execute(sql)
    tables = cursor.fetchall()
    if table_name in str(tables):
        sql = "drop table {}".format(table_name)
        cursor.execute(sql)

# def insert_person_information(table_user,name, birthday, householder, relation, gender , nationality, address,domicile_registration,education_level,marriage_status):
#     db = pymysql.connect(host='localhost', user='root', password='chkx123', port=3306, db=database)
#     cursor = db.cursor()
#     # ###若有相同的评论，则跳过
#
#     sql = "insert into {} values(%s,%s,%s,%s,%s,%s,%s,%s)".format(table_user)
#     cursor.execute(sql,
#                    (name, birthday, householder, relation, gender, nationality, address, domicile_registration,education_level,marriage_status))
#     db.commit()
#     db.close()

# def build_sight_user_comment_demo() -> None:
#     db = pymysql.connect(host='localhost', user='root', password='12345678', port=3306, db=database)
#     cursor = db.cursor()
#     up_table = "city_demo"
#     table = "sight_user_comment_demo"
#
#     sql = "CREATE TABLE {}(city_name VARCHAR(10)," \
#           "sight_name VARCHAR(20)," \
#           "username VARCHAR(30)," \
#           "user_id INT," \
#           "user_score VARCHAR(5)," \
#           "user_comment TEXT," \
#           "user_location VARCHAR(15)," \
#           "comment_time VARCHAR(15)," \
#           "CONSTRAINT fk_city_name_demo FOREIGN KEY(city_name) REFERENCES {}(city_name) ON UPDATE CASCADE ON DELETE RESTRICT)".format(
#         table, up_table)
#     cursor.execute(sql)


# def delete_txt():
#     data_path = r'C:\Users\18353\Desktop\大学\毕业设计\景点推荐系统\程序\下载的数据'
#     os.chdir(data_path)
#     flag = os.path.exists('ori_url.txt')
#
#     if flag is True:
#         os.remove('ori_url.txt')


# drop_table(table_person)
# build_person_information_demo()